if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[v_Articles]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[v_Articles]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE VIEW dbo.v_Articles
AS
SELECT TOP 100 PERCENT dbo.ForumPosts.PostID, dbo.ForumPosts.CategoryID, 
      dbo.ForumPosts.ParentPostID, dbo.ForumPosts.Subject, dbo.ForumPosts.Body, 
      dbo.ForumPosts.CreateDate, dbo.ForumPosts.Status, dbo.ForumPosts.Summary, 
      dbo.ForumPosts.Type, dbo.ForumPosts.UserID, dbo.ForumPosts.NumberOfVisits, 
      dbo.ForumPosts.PartnerID, dbo.ForumPosts.OriginAuthor, dbo.ForumPosts.URL, 
      dbo.ForumPosts.OriginalDate, dbo.ForumPosts.LastModifiedBy, 
      dbo.ForumPosts.LastModifiedDateTime, dbo.ForumPosts.CreateUser, 
      dbo.Article.ArticleID, dbo.Article.Topic, dbo.Article.SubTopic, dbo.Article.BibleTitle, 
      dbo.Article.BibleChapter, dbo.Article.BibleSection, dbo.Article.BibleChapter_To, 
      dbo.Article.BibleSection_To, dbo.Article.Vote_yes, dbo.Article.Vote_no, 
      dbo.Article.ApproveOpinion, dbo.Article.ApproveUserID, dbo.Article.ApproveTime, 
      dbo.Users.Email, dbo.Users.NickName, dbo.ForumCategories.CategoryName, 
      Users_1.NickName AS ApproveUser
FROM dbo.Users Users_1 RIGHT OUTER JOIN
      dbo.Article ON Users_1.UserID = dbo.Article.ApproveUserID RIGHT OUTER JOIN
      dbo.Users INNER JOIN
      dbo.ForumPosts ON dbo.Users.UserID = dbo.ForumPosts.UserID LEFT OUTER JOIN
      dbo.ForumCategories ON 
      dbo.ForumPosts.CategoryID = dbo.ForumCategories.CategoryID ON 
      dbo.Article.PostID = dbo.ForumPosts.PostID
ORDER BY dbo.Article.Vote_yes DESC

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

